<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/3/16
 * Time: 17:54
 */

namespace app\console\controller;
use app\BaseController;
use app\console\model\Admin;
use app\console\model\AdminRole;


use think\facade\Cache;
use think\facade\Db;
use think\facade\Log;
use think\facade\Request;
use think\facade\Route;
use think\facade\Session;
use think\facade\View;
use WordAnalysis\Analysis;
use Yurun\Util\Chinese;
use Yurun\Util\Chinese\Pinyin;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class Base extends BaseController
{
    protected $controller = null;
    protected $where = [];
    protected $model;
    protected $order = ['id'=>'desc'];
    protected $list_rows = 100;
    protected $excel = [];
    public function initialize()
    {
        //parent::initialize(); // TODO: Change the autogenerated stub
//        Db::event('before_select', function ($query) {
//
//        });
        $admin = Admin::find(Session::get('admin_id'));

        //Session::set('data_id_str',$admin['data_id_str']);
        $role_pri = \app\model\Company::select();
        Session::save();
        $this->where[] = [];
        //
        View::assign('role_pri',$role_pri);

    }

    public function ArrayOneToTwo($value,$is_serialize = true)
    {
        $picarr = input($value);
        if(!empty($picarr)&&is_array($picarr))
        {
            $info = input('info/a');
            $show = input('show/a');
            $arr = array();
            foreach($picarr as $key=>$v){
                $row['img']  = $v;
                if(!empty($info)) $row['info'] = $info[$key];
                $row['show'] = $show[$key];
                $arr[]=$row;
            }
            if($is_serialize != true){
                return  $arr;
            }
            return serialize($arr);
        }else{
            return '';
        }

    }

    /**
     * @return mixed
     * 查询
     */
    public function index()
    {
        $param = Request::param();



        $data = $this->model::withSearch(array_keys($param), $param)->lock(true)->order($this->order)->where($this->where)->paginate(['query' => $param, 'list_rows' => $this->list_rows])->each(function ($item, $key) {

        });

        $this->select();
        return View::fetch('', ['data' => $data]);
    }

    /**
     * 添加
     * @return mixed
     */
    public function create()
    {

        if ($this->request->isPost()) {
            $param = $this->request->param();
            $ret = $this->model::create($param);
            if ($ret) {
                win('操作成功', Route::buildUrl('index',['company_id'=>input('company_id')]));
            }
            fail('操作失败');
        }
        $this->select();
        return View::fetch('', ['info' => []]);
    }



    /**
     * 修改
     * @param $id
     * @return mixed
     */
    public function update()
    {
        $param = $this->request->param();
        Db::starttrans();
        $info = (new $this->model)->lock(true)->find($param['id']);
        if ($this->request->isPost()) {

            try {

                $ret = $info->save($param);
                if($ret === false){
                    fail('修改失败');
                }
                Db::commit();
            } catch (\Exception $e) {
                Db::rollback();
                fail('修改失败');
            }
            win('修改成功', Route::buildUrl('index',['company_id'=>input('company_id')]));
        }


        $this->select();
        return View::fetch('create', ['info' => $info]);
    }

    public function select()
    {
        $level_data = [
           ['id'=>0,'title'=>'游客'],
            ['id'=>1,'title'=>'会员'],
            ['id'=>2,'title'=>'经销商'],
            ['id'=>3,'title'=>'总代理']
        ];
        View::assign('level_data',$level_data);
    }

    /**
     * 删除方法
     * @param $id
     */
    public function destory($id)
    {
        if ($this->request->isGet()) {
            $ret = $this->model::destroy($id);
            if ($ret) jump('删除成功', Route::buildUrl('index',['company_id'=>input('company_id')]));
            fail('删除失败');
        }
    }

    /**
     * 软删除数据恢复方法
     * @param $id
     */
    public function restore($id)
    {
        $user = $this->model::onlyTrashed()->find($id);
        $ret = $user->restore();
        if ($ret === true) {
            jump('恢复成功', Route::buildUrl('index'));
        }
    }

    /**
     * 公共切换状态
     * @return bool|int
     */
    public function check()
    {
        $inquire = $this->request->param();
        $ret = (new $this->model())->where('id', $inquire['id'])->value($inquire['check']) == '1' ? 2 : 1;
        if ((new $this->model())->where('id', $inquire['id'])->update([$inquire['check'] => $ret])) {
            return $ret;
        }
        return false;
    }


    /**
     * 首页批量更新
     */
    public function index_update()
    {
        $param = $this->request->param();
        $data = [];
        if(!empty($param['sort'])){
            foreach ($param['sort'] as $k => $v) {
                $data[$k]['id'] = $k;
                $data[$k]['sort'] = $v;
                // $ret = Db::name($this->controller)->where('id',$k)->setField('sort',$v);
            }

        }
        $ret = (new $this->model())->allowField(['id','sort'])->saveAll($data);
        if ($ret !== false) {
            jump('更新成功', Route::buildUrl('index'));
        }
    }

    public function setField()
    {

        $param = Request::param();
        $ret = $this->model::where('id', $param['id'])->save([$param['field'] => $param['value']]);

        if ($ret) {
            return json(['code' => 200, 'message' => '修改成功']);
        }
        return json(['code' => 400, 'message' => '没有修改']);
    }

    /**
     * 审核状态按钮
     */
    public function audit_status()
    {
        $param = Request::param();

        $ret = $this->model::where('id',$param['id'])->where('audit_status',1)->update(['audit_status' => 2]);
        if($ret){
            return json(['code' => 200, 'msg' => '审核成功']);
        }
        return json(['code' => 400, 'msg' => '失败']);
    }

    /*
     * 获取所有数据
     */
    public function all()
    {
        $tab_id = $this->request->param('tab_id');
        $data = $this->model::where('company_id',$tab_id)->select();
        return json(['code'=>200,'msg'=>'全部数据','data'=>$data]);
    }



    /*
     * excel导出
     */
    public function write($data = [], $filename = '订单导出', $set_title = '表格1')
    {

        //$filename =  '订单导出';
        //$set_title = '订单导出';
        $spreadsheet = new \app\common\SpreadSheet();
        require_once root_path() . 'extend/phpoffice/phpexcel/Classes/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        ini_set("memory_limit", "1024M"); // 设置php可使用内存
        set_time_limit(0);
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }

        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");

        /*----------------------- 设置导出标题------------------------*/
        $objPHPExcel->setActiveSheetIndex(0);
        $az = [
            'A' => 30,
            'B' => 20,
            'C' => 20,
            'D' => 20,
            'E' => 20,
            'F' => 20,
            'G' => 20,
            'H' => 20,
            'I' => 30,
            'J' => 20,
            'K' => 20,
            'L' => 20,
            'M' => 20,
            'N' => 20,
            'O' => 20,
            'P' => 20,
            'Q' => 20,
            'R' => 20,
            'S' => 20,
            'T' => 20,
            'U' => 20,
            'V' => 20,
            'W' => 20,
            'X' => 20,
            'Y' => 20,
            'Z' => 20
        ];

        $az2 = array_keys($az);//给data数组循环使用
        $th = [
            '订单号' => 'id_card',
            '姓名' => 'address',
//            '手机号' => 'mobile',
//            '主管' => 'zhuguan',
//            '教官' => 'jiaoguan',
//            '订单价格' => 'order_price',
//            '配送方式' => 'distribution_type',
//            '省市区' => 'pca',
//            '地址' => 'address',
//            '状态' => 'status_text',
//            '创建时间' => 'create_time'
        ];
        $th2 = array_keys($th);//
        $th3 = array_values($th);//索引数组转关联数组
        $az_i = 0;

        foreach ($az as $az_k => $az_v) {
            //设置第一列导出标题
            if (!empty($th2[$az_i])) {
                $objPHPExcel->getActiveSheet()->setCellValue($az_k . '1', $th2[$az_i]);
                //设置单元列的宽度
                $objPHPExcel->getActiveSheet()->getColumnDimension($az_k)->setWidth($az_v);
                $az_i++;
            }

        }

        foreach ($data as $k => $v) {
            $i = $k + 2; //循环列 A2 A3 A4
            $k2_i = 0;//循环行 A2 B2 C3

            foreach ($th3 as $ava) {
                $objPHPExcel->getActiveSheet()->setCellValue($az2[$k2_i] . ($i), "\t" . $v[$ava]);//循环行
                $k2_i++;
            }
        }


        $objPHPExcel->getActiveSheet()->setTitle($set_title);
        echo date('H:i:s'), " 设置格式为Excel2007版格式", EOL;
        $callStartTime = microtime(true);

        //$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;charset=utf-8;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');

        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

        echo date('H:i:s'), " 设置生成的文件为： ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
        echo date('H:i:s'), ' 写入Workbook中耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
        echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 完成写入文件", EOL;
        echo date('H:i:s'), ' 文件被创建在： ', getcwd(), '目录', EOL;
    }
    public static function actionExcelData()
    {
        require_once root_path().'extend/phpoffice/phpexcel/Classes/PHPExcel.php';
        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        $excelInfo['path'] = '/file/456.xlsx';
        $inputFileType = IOFactory::identify($excelInfo['path']);
        $objReader = IOFactory::createReader($inputFileType);
        $worksheetNames = $objReader->listWorksheetNames($excelInfo['path']);
        #只读取表格数据，忽略里面的各种格式,否则会内存耗尽
        $objReader->setReadDataOnly(TRUE);
        $objReader->setLoadSheetsOnly($worksheetNames[0]);#笔者此处加载第11个sheet
        $objPHPExcels = $objReader->load($excelInfo['path']);
        $maxCol = $objPHPExcels->getSheet(0)->getHighestColumn();#总列数
        $maxCol = 'I';
        $maxRow = $objPHPExcels->getSheet(0)->getHighestRow();#总行数
        $a = 'A';
        for ($i = 1; $i <= $maxRow; $i++) {
            yield  $objPHPExcels->getSheet(0)->rangeToArray('A' . $i . ':' . $maxCol . $i)[0];#读取一行
            $a++;
        }
    }

    /*
     * excel导入
     */
    public function read2(){

        require_once root_path().'extend/phpoffice/phpexcel/Classes/PHPExcel.php';
//        halt(\request()->file('data')->getPathname());
//        halt($_FILES['data']['tmp_name']);
        // 获取 excel 中的文件内容
        $objPHPExcel = \PHPExcel_IOFactory::load(\request()->file('data')->getPathname());
        // 逐行读取 sheet 内的内容（常用）
        foreach ($objPHPExcel->getWorksheetIterator() as $sheet){
            //循环sheet
            foreach ($sheet->getRowIterator() as $row){
                //循环row
                if ($row->getRowIndex() <= 1){
                    // 1的时候默认从第二行开始
                    continue;
                }
                $tr = [];
                foreach ($row->getCellIterator() as $cell){     //循环cell
                    $v = $cell->getValue();

                    $tr[] = $v;
                }
                $tr = unsetNull($tr);
               // dump($tr);
//                $persion = $this->model::where('sms_id',$tr[0])->where('sms_id',$tr[1])->where('sms_id',$tr[2])->find();
//                if(empty($persion)){
//                    $this->model::create(['sms_id'=>$tr[0],'name'=>$tr[1],'mobile'=>$tr[2]]);
//                }
            }
        }
        win('导入成功');

    }

    public function read()
    {
        require_once root_path() . '/vendor/autoload.php';
        set_time_limit(0);
        ini_set("memory_limit", "2024M");
        $spreadsheet = IOFactory::load($_FILES['excel']['tmp_name']);
        $sheet = $spreadsheet->getActiveSheet();
        # 获取总列数
        $getHighestColumnString = $sheet->getHighestColumn();//J
        $column = ['A'=>1,'B'=>2,'C'=>3,'D'=>4,'E'=>5,'F'=>6,'G'=>7,'H'=>8,'I'=>9,'J'=>10,'K'=>11,'L'=>12];
        $getHighestColumn = $column[$getHighestColumnString];
        # 获取总行数
        $getHighestRow = $sheet->getHighestRow();

        # 列数 改为数字显示

        for ($i = 2; $i <= $getHighestRow; $i++) {
            $data = [];
            for ($j = 1; $j <= $getHighestColumn; $j++) {
                $data[$i][$j] = $sheet->getCellByColumnAndRow($j, $i)->getValue();//
            }
            $this->controller::import_data($data);

        }


    }

    public function writ2e(){
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValueByColumnAndRow(1,1, '唯一标识');
    }
}